Namespace Core
  Public Class Table

#Region "Private Members"
    Private _parent As Kernel

    Private mSchema As String
    Private mName As String
    Private _HistorySchema As String
    Private _HistoryName As String
    Private mUserColumns As New List(Of Column)
    Private mSystemColumns As New SortedList(Of String, Column)
    Private mOldStatus As TableStatuses
    Private mOldLogging As LogActions
    Private mNewLogging As LogActions
#End Region

#Region "Constructors"
    Sub New(ByVal parent As Kernel, ByVal row As DataRow)
      Dim query As String
      Dim cn As SqlConnection
      Dim cmd As SqlCommand
      Dim item As Column
      Dim dr As SqlDataReader
      Dim srcField As String
      Dim logField As String
      Dim i As Integer

      ' ======================================================================
      ' === PRE-PROCESS                                                    ===
      ' ======================================================================
      ' === Save the parameters ==============================================
      _parent = parent
      mSchema = row("tblSchema")
      mName = row("tblName")
      _HistorySchema = row("tblSchemaHistory") & ""
      _HistoryName = row("tblNameHistory") & ""

      cn = Connection(_parent.ConnectionString)
      cmd = cn.CreateCommand

      ' ======================================================================
      ' === PROCESS                                                        ===
      ' ======================================================================
      ' === Pre-fill the system columns ======================================
      mSystemColumns.Add(COL_KEY, New Column(Me, COL_KEY, "uniqueidentifier", 0, 0, 0, "NEWID()", False))
      mSystemColumns.Add(COL_CREATED_ON, New Column(Me, COL_CREATED_ON, "datetime", 0, 0, 0, "GETDATE()", False))
      mSystemColumns.Add(COL_CREATED_BY_USER, New Column(Me, COL_CREATED_BY_USER, "varchar", 50, 50, 0, "SUSER_SNAME()", False))
      mSystemColumns.Add(COL_CREATED_BY_APPLICATION, New Column(Me, COL_CREATED_BY_APPLICATION, "varchar", 50, 50, 0, "'?'", False))
      mSystemColumns.Add(COL_CREATED_BY_MACHINE, New Column(Me, COL_CREATED_BY_MACHINE, "varchar", 50, 50, 0, "HOST_NAME()", False))
      mSystemColumns.Add(COL_UPDATED_ON, New Column(Me, COL_UPDATED_ON, "datetime", 0, 0, 0, "GETDATE()", True))
      mSystemColumns.Add(COL_UPDATED_BY_USER, New Column(Me, COL_UPDATED_BY_USER, "varchar", 50, 50, 0, "SUSER_SNAME()", True))
      mSystemColumns.Add(COL_UPDATED_BY_APPLICATION, New Column(Me, COL_UPDATED_BY_APPLICATION, "varchar", 50, 50, 0, "'?'", True))
      mSystemColumns.Add(COL_UPDATED_BY_MACHINE, New Column(Me, COL_UPDATED_BY_MACHINE, "varchar", 50, 50, 0, "HOST_NAME()", True))
      mSystemColumns.Add(COL_UPDATED_AS, New Column(Me, COL_UPDATED_AS, "char", 1, 1, 0, "'C'", True))

      ' === Retrieve the columns =============================================
      query = String.Format("SELECT C.name AS colName, T.name AS colType, C.length AS colLength, ISNULL(C.prec,0) AS colPrecision, C.xscale AS colScale FROM sysColumns C, sysTypes T WHERE C.xtype=T.xtype AND T.name NOT IN ('sysname', 'id', 'tid', 'empid') AND id = OBJECT_ID('[{0}].[{1}].[{2}]') ORDER BY colName", _parent.InitialCatalog, mSchema, mName)
      cmd.CommandText = query
      dr = cmd.ExecuteReader
      While dr.Read
        item = New Column(Me, dr)
        If dr("colName").ToString().StartsWith(PFX_SCHEMA) = True Then
          item = mSystemColumns(dr("colName"))
          item.OldStatus = ColumnStatuses.Installed ' If found, then it was installed
          item.SetInstallAction(ColumnActions.None)
        Else
          mUserColumns.Add(item)
        End If
      End While
      dr.Close()

      ' === Retrieve the history table =======================================
      If _HistoryName IsNot Nothing AndAlso _HistoryName.Length > 0 Then
        query = String.Format("SELECT name AS colName FROM sysColumns WHERE id = OBJECT_ID('[{0}].[{1}].[{2}]')", _parent.InitialCatalog, _HistorySchema, _HistoryName)
        cmd.CommandText = query
        dr = cmd.ExecuteReader
        While dr.Read
          logField = dr("colName")
          If logField.StartsWith(PFX_OLD) Then
            srcField = logField.Remove(0, PFX_OLD.Length)
          ElseIf logField.StartsWith(PFX_NEW) Then
            srcField = logField.Remove(0, PFX_NEW.Length)
          Else
            srcField = Nothing
          End If
          If srcField IsNot Nothing Then
            'item = mUserColumns(srcField)
            item = Nothing
            For Each item In mUserColumns
              If item.Name = srcField Then Exit For
            Next
            item.OldStatus = ColumnStatuses.Installed ' If found, then it was installed
            item.SetInstallAction(ColumnActions.None)
          End If
        End While
        dr.Close()
      End If

      ' === Retrieve the triggers ============================================
      ' query = String.Format("SELECT name FROM sysObjects WHERE xtype='TR' AND name IN ('{0}{3}','{1}{3}','{2}{3}')", PFX_TRI, PFX_TRU, PFX_TRD, mName)
      query = String.Format("SELECT ISNULL((SELECT 1 FROM sysObjects WHERE xtype='TR' AND name='{0}{3}'),0)" _
                                & "+ISNULL((SELECT 2 FROM sysObjects WHERE xtype='TR' AND name='{1}{3}'),0)" _
                                & "+ISNULL((SELECT 4 FROM sysObjects WHERE xtype='TR' AND name='{2}{3}'),0)", PFX_TRI, PFX_TRU, PFX_TRD, mName)
      cmd.CommandText = query
      mOldLogging = cmd.ExecuteScalar

      ' === Set the table's status ===========================================
      i = 0
      For Each item In mSystemColumns.Values
        If item.OldStatus = ColumnStatuses.Installed Then
          i += 1
        End If
      Next

      mOldStatus = IIf(i > 0, TableStatuses.Minimum, TableStatuses.None) _
                Or IIf((_HistoryName & "").Length > 0, TableStatuses.Standard, TableStatuses.None)
      SetInstallAction(TableActions.None)

      ' ======================================================================
      ' === POST-PROCESS                                                   ===
      ' ======================================================================
      cn.Close()
    End Sub
#End Region

#Region "Properties"
    ReadOnly Property Key() As String
      Get
        Return String.Format("{0}.{1}", mSchema, mName)
      End Get
    End Property
    ReadOnly Property Parent() As Kernel
      Get
        Return _parent
      End Get
    End Property

    ReadOnly Property Schema() As String
      Get
        Return mSchema
      End Get
    End Property

    ReadOnly Property Name() As String
      Get
        Return mName
      End Get
    End Property

    ReadOnly Property HistorySchema() As String
      Get
        Return _HistorySchema
      End Get
    End Property

    ReadOnly Property HistoryName() As String
      Get
        Return _HistoryName
      End Get
    End Property

    ReadOnly Property UserColumns() As List(Of Column)
      Get
        Return mUserColumns
      End Get
    End Property

    ReadOnly Property SystemColumns() As SortedList(Of String, Column)
      Get
        Return mSystemColumns
      End Get
    End Property

    ReadOnly Property OldStatus() As TableStatuses
      Get
        Return mOldStatus
      End Get
    End Property

    Property NewStatus() As TableStatuses
      Get
        Dim sysCount As Integer
        Dim usrCount As Integer
        Dim item As Column
        Dim rc As TableStatuses

        For Each item In mSystemColumns.Values
          If item.NewStatus = ColumnStatuses.Installed AndAlso item.Name <> COL_KEY Then sysCount += 1
        Next

        For Each item In mUserColumns
          If item.NewStatus = ColumnStatuses.Installed Then usrCount += 1
        Next

        rc = IIf(sysCount > 0, TableStatuses.Minimum, TableStatuses.None) _
          Or IIf(usrCount > 0, TableStatuses.Standard, TableStatuses.None)

        Return rc
      End Get
      Set(ByVal value As TableStatuses)
        Select Case value
          Case TableStatuses.None
            SetInstallAction(TableActions.Remove)
          Case TableStatuses.Minimum
            SetInstallAction(TableActions.InstallMinimum)
          Case TableStatuses.Standard
            SetInstallAction(TableActions.InstallStandard)
          Case TableStatuses.Complete
            If _parent.Edition = Editions.Personal Then Throw New ArgumentException("eyeLog Personal Edition does not support Complete install")
            SetInstallAction(TableActions.InstallComplete)
        End Select
      End Set
    End Property

    Property NewStatus2() As Integer
      Get
        Return NewStatus
      End Get
      Set(ByVal value As Integer)
        NewStatus = value
      End Set
    End Property

    ReadOnly Property OldLogging() As LogActions
      Get
        Return mOldLogging
      End Get
    End Property

    ReadOnly Property NewLogging() As LogActions
      Get
        Return mNewLogging
      End Get
    End Property
#End Region

#Region "Methods"
    Public Sub SetInstallAction(ByVal action As TableActions)
      Dim item As Column
      Dim usrAction As ColumnActions
      Dim sysAction As ColumnActions

      Select Case action
        Case TableActions.None
          sysAction = ColumnActions.None
          usrAction = ColumnActions.None
          mNewLogging = mOldLogging
        Case TableActions.InstallMinimum
          sysAction = ColumnActions.Install
          usrAction = ColumnActions.Remove
          mNewLogging = LogActions.None
        Case TableActions.InstallStandard
          sysAction = ColumnActions.Remove
          usrAction = ColumnActions.Install
          mNewLogging = LogActions.Insert Or LogActions.Update Or LogActions.Delete
        Case TableActions.InstallComplete
          If _parent.Edition = Editions.Personal Then Throw New ArgumentException("eyeLog Personal Edition does not support Complete install")
          sysAction = ColumnActions.Install
          usrAction = ColumnActions.Install
          mNewLogging = LogActions.Insert Or LogActions.Update Or LogActions.Delete
        Case TableActions.Remove
          sysAction = ColumnActions.Remove
          usrAction = ColumnActions.Remove
          mNewLogging = LogActions.None
#If DEBUG Then
        Case Else
          Stop
          sysAction = ColumnActions.None
          usrAction = ColumnActions.None
          mNewLogging = LogActions.None
#End If
      End Select

      ' === Update system columns ========================================
      For Each item In mSystemColumns.Values
        item.SetInstallAction(sysAction)
      Next

      ' === Update user columns ==========================================
      For Each item In mUserColumns
        Try
          item.SetInstallAction(usrAction)
        Catch ex As Exception
          MsgBox(String.Format("Error on column [{1}]:{0}{2}", vbCrLf, item.Name, ex.Message))
        End Try
      Next
    End Sub

    Public Sub SetLogging(ByVal onInsert As Boolean, ByVal onUpdate As Boolean, ByVal onDelete As Boolean)
      mNewLogging = IIf(onInsert = True, LogActions.Insert, LogActions.None) _
                 Or IIf(onUpdate = True, LogActions.Update, LogActions.None) _
                 Or IIf(onDelete = True, LogActions.Delete, LogActions.None)
    End Sub

    Public Sub GetScript(ByVal script As List(Of String))
      '      Dim rc As New System.Text.StringBuilder
      Dim item As Column
      'Dim colId As New Column(Me, COL_KEY, "uniqueidentifier", 0, 0, 0, "NEWID()")
      Dim colId As Column
      Dim info As String

      ' ======================================================================
      ' === PRE-PROCESS                                                    ===
      ' ======================================================================
      info = String.Format("--- Table: [{0}].[{1}]", mSchema, mName)
      script.Add(String.Format("{0}{1}", info, New String("-", 78 - info.Length)))

      ' ======================================================================
      ' === PROCESS                                                        ===
      ' ======================================================================
      ' === Process System Columns ===========================================
      colId = mSystemColumns(COL_KEY)
      colId.SetInstallAction(IIf(NewStatus <> TableStatuses.None, ColumnActions.Install, ColumnActions.Remove))

      For Each item In mSystemColumns.Values
        item.GetScript(script)
      Next

      ' === Process History Table ============================================
      GetScriptLogging(script)

      ' === Script the triggers ==============================================
      GetScriptTriggers(script)

      ' ======================================================================
      ' === POST-PROCESS                                                   ===
      ' ======================================================================
    End Sub

    Private Sub GetScriptLogging(ByVal script As List(Of String))
      Dim rc As New System.Text.StringBuilder
      Dim item As Column
      Dim newSchema As String
      Dim newName As String
      Dim colAs As New Column(Me, COL_UPDATED_AS, "char", 1, 0, 0, "'I'", True)

      If ((NewStatus And TableStatuses.Standard) <> TableStatuses.Standard) Then
        ' --- Drop the history table -----------------------------------------
        If ((_HistoryName & "").Length > 0) Then
          rc.AppendLine(GetScriptDropObject(_HistorySchema, _HistoryName, ObjectTypes.Table))
        End If
      Else
        If (_HistoryName & "").Length = 0 Then
          ' --- Create the history table -------------------------------------
          If _parent.UseSchema = True Then
            newSchema = PFX_SCHEMA
            newName = mName
          Else
            newSchema = mSchema
            newName = String.Format("{0}_{1}", PFX_SCHEMA, mName)
          End If
          rc.AppendFormat("{0}CREATE TABLE [{1}].[{2}] (", vbCrLf, newSchema, newName)
          rc.AppendFormat("{0}   {1},", vbCrLf, mSystemColumns(COL_KEY).ToString2())
          rc.AppendFormat("{0}   {1},", vbCrLf, mSystemColumns(COL_UPDATED_ON).ToString2())
          rc.AppendFormat("{0}   {1},", vbCrLf, mSystemColumns(COL_UPDATED_BY_USER).ToString2())
          rc.AppendFormat("{0}   {1},", vbCrLf, mSystemColumns(COL_UPDATED_BY_MACHINE).ToString2())
          rc.AppendFormat("{0}   {1},", vbCrLf, colAs.ToString2())
          For Each item In mUserColumns
            If item.NewStatus = ColumnStatuses.Installed Then
              rc.AppendFormat("{0}   {1},", vbCrLf, item.ToString2().Replace("[", String.Format("[{0}", PFX_OLD)))
              rc.AppendFormat("{0}   {1},", vbCrLf, item.ToString2().Replace("[", String.Format("[{0}", PFX_NEW)))
            End If
          Next
          rc.Remove(rc.Length - 1, 1)
          rc.AppendLine(");")
        Else
          ' --- Update the history table -------------------------------------
          For Each item In mUserColumns
            item.GetScript(script)
          Next
        End If
      End If

      AddScriptItem(script, rc.ToString())
    End Sub

    Private Sub GetScriptTriggers(ByVal script As List(Of String))
      Dim newSchema As String
      Dim newName As String
      Dim item As Column
      Dim usrFields As New System.Text.StringBuilder
      Dim sysFields As String
      Dim sysValues As String
      Dim temp As New System.Text.StringBuilder

      If _parent.UseSchema = True Then
        newSchema = PFX_SCHEMA
        newName = mName
      Else
        newSchema = mSchema
        newName = String.Format("{0}_{1}", PFX_SCHEMA, mName)
      End If

      For Each item In mUserColumns
        If item.NewStatus = ColumnStatuses.Installed Then
          usrFields.AppendFormat("{0}{1},", "{0}", item.Name)
        End If
      Next

      sysFields = String.Format("{0},{1},{2},{3},{4}", COL_KEY, COL_UPDATED_ON, COL_UPDATED_BY_USER, COL_UPDATED_BY_MACHINE, COL_UPDATED_AS)
      sysValues = String.Format("{0},{1},{2},{3},{4}", _
                                String.Format("{0}{1}", "{1}", COL_KEY), _
                                mSystemColumns(COL_UPDATED_ON).DefaultValue, _
                                mSystemColumns(COL_UPDATED_BY_USER).DefaultValue, _
                                mSystemColumns(COL_UPDATED_BY_MACHINE).DefaultValue, _
                                "'{0}'")

      AddScriptItem(script, GetScriptDropObject(mSchema, String.Format("{0}{1}", PFX_TRI, mName), ObjectTypes.Trigger))
      script.Add("GO")
      script.Add(Nothing)
      If ((NewStatus And TableStatuses.Standard) = TableStatuses.Standard) AndAlso ((mNewLogging And LogActions.Insert) = LogActions.Insert) Then
        temp = New System.Text.StringBuilder
        temp.AppendFormat(vbCrLf & "CREATE TRIGGER [{0}{2}] ON [{1}].[{2}] AFTER INSERT AS", PFX_TRI, mSchema, mName)
        temp.AppendFormat(vbCrLf & "BEGIN")
        temp.AppendFormat(vbCrLf & "   SET NOCOUNT ON;")
        temp.AppendFormat(vbCrLf & "   INSERT INTO [{0}].[{1}] ({2}{3})", newSchema, newName, String.Format(usrFields.ToString, PFX_NEW), sysFields)
        temp.AppendFormat(vbCrLf & "   SELECT {0}{1}", String.Format(usrFields.ToString, ""), String.Format(sysValues, "I", ""))
        temp.AppendFormat(vbCrLf & "   FROM Inserted;")
        temp.AppendFormat(vbCrLf & "END;")
        AddScriptItem(script, temp.ToString)
        script.Add("GO")
        script.Add(Nothing)
      End If

      AddScriptItem(script, GetScriptDropObject(mSchema, String.Format("{0}{1}", PFX_TRU, mName), ObjectTypes.Trigger))
      script.Add("GO")
      script.Add(Nothing)
      If NewStatus <> TableStatuses.None Then
        temp = New System.Text.StringBuilder
        temp.AppendFormat(vbCrLf & "CREATE TRIGGER [{0}{2}] ON [{1}].[{2}] AFTER UPDATE AS", PFX_TRU, mSchema, mName)
        temp.AppendFormat(vbCrLf & "BEGIN")
        temp.AppendFormat(vbCrLf & "   SET NOCOUNT ON;")
        If (NewStatus And TableStatuses.Minimum) = TableStatuses.Minimum Then
          temp.AppendFormat(vbCrLf & "   UPDATE [{0}].[{1}] SET ", mSchema, mName)
          'temp.AppendFormat(vbCrLf & "     {0}={1},", COL_UPDATED_ON, mSystemColumns(COL_UPDATED_ON).DefaultValue)
          'temp.AppendFormat(vbCrLf & "     {0}={1},", COL_UPDATED_BY_USER, mSystemColumns(COL_UPDATED_BY_USER).DefaultValue)
          'temp.AppendFormat(vbCrLf & "     {0}={1},", COL_UPDATED_BY_MACHINE, mSystemColumns(COL_UPDATED_BY_MACHINE).DefaultValue)

          For Each item In mSystemColumns.Values
            If item.NewStatus = ColumnStatuses.Installed AndAlso item.UpdateOnUpdate = True Then
              temp.AppendFormat(vbCrLf & "      {0}={1},", item.Name, IIf(item.Name = COL_UPDATED_AS, "'U'", item.DefaultValue))
            End If
          Next

          temp.Remove(temp.Length - 1, 1)
          temp.AppendFormat(vbCrLf & "   WHERE {0} IN (SELECT {0} FROM Inserted);", COL_KEY)
        End If

        If (NewStatus And TableStatuses.Standard) = TableStatuses.Standard AndAlso (mNewLogging And LogActions.Update) = LogActions.Update Then
          temp.AppendFormat(vbCrLf & "      INSERT INTO [{0}].[{1}] ({2}{3}{4})", newSchema, newName, String.Format(usrFields.ToString, PFX_OLD), String.Format(usrFields.ToString, PFX_NEW), sysFields)
          temp.AppendFormat(vbCrLf & "      SELECT {0}{1}{2} ", String.Format(usrFields.ToString, "D."), String.Format(usrFields.ToString, "I."), String.Format(sysValues, "U", "I."))
          temp.AppendFormat(vbCrLf & "      FROM Inserted I FULL OUTER JOIN Deleted D ON I.{0}=D.{0};", COL_KEY)
        End If
        temp.AppendFormat(vbCrLf & "END;")
        AddScriptItem(script, temp.ToString)
        script.Add("GO")
        script.Add(Nothing)
      End If

      AddScriptItem(script, GetScriptDropObject(mSchema, String.Format("{0}{1}", PFX_TRD, mName), ObjectTypes.Trigger))
      script.Add("GO")
      script.Add(Nothing)
      If ((NewStatus And TableStatuses.Standard) = TableStatuses.Standard) AndAlso (mNewLogging And LogActions.Delete) = LogActions.Delete Then
        temp = New System.Text.StringBuilder
        temp.AppendFormat(vbCrLf & "CREATE TRIGGER [{0}{2}] ON [{1}].[{2}] AFTER DELETE AS", PFX_TRD, mSchema, mName)
        temp.AppendFormat(vbCrLf & "BEGIN")
        temp.AppendFormat(vbCrLf & "   SET NOCOUNT ON;")
        temp.AppendFormat(vbCrLf & "   INSERT INTO [{0}].[{1}] ({2}{3})", newSchema, newName, String.Format(usrFields.ToString, PFX_OLD), sysFields)
        temp.AppendFormat(vbCrLf & "   SELECT {0}{1}", String.Format(usrFields.ToString, ""), String.Format(sysValues, "D", ""))
        temp.AppendFormat(vbCrLf & "   FROM Deleted;", String.Format(usrFields.ToString, ""))
        temp.AppendFormat(vbCrLf & "END;")
        AddScriptItem(script, temp.ToString)
        script.Add("GO")
        script.Add(Nothing)
      End If
    End Sub
#End Region
  End Class
End Namespace